# -*- coding:utf-8 -*-
import pymysql
# 连接数据库
conn = pymysql.connect(host="localhost", user="root", passwd="123456", database="login1", charset="utf8")
print('服务器连接成功!')
# 创建cursor游标
cursor = conn.cursor()
try:
    # 一次插入多条记录
    sql = "select * from logintable"
    rs = cursor.execute(sql)
    conn.commit()  # 提交更新的数据到数据库
    print(rs)
    print('插入成功')
except Exception as error:  # 插入失败后，抛出异常
    print('插入数据失败')
    print(error)
conn.close()
print('服务器关闭成功')

'''将爬取的数据存入数据库中将爬取的数据存入数据库中将爬取的数据存入数据库中将爬取的数据存入数据库中将爬取的数据存入数据库中将爬取的数据存入数据库中将爬取的数据存入数据库中将爬取的数据存入数据库中'''
import pymysql

host = "localhost"
user = "root"  # mysql中你的用户名
pwd = "xxx"  # 你的root对应的密码
dbName = "doubanRecord"  # 将存储数据的数据库名

try:
    connection = pymysql.connect(host=host, user=user, password=pwd, db=dbName, charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
except pymysql.err.InternalError as err:
    if (err.args[0] == 1049):
        print(err.args[1], "  ", "need create db")
        connection = pymysql.connect(host=host, user=user, password=pwd, charset='utf8mb4',
                                     cursorclass=pymysql.cursors.DictCursor)
        with connection.cursor() as cursor:
            cursor.execute("CREATE DATABASE " + dbName)  # 创建数据库
        connection.commit()  # 如果是修改数据库内容的，需要加上这一句
finally:
    with connection.cursor() as cursor:
        try:
            # 使用预处理语句创建表，[表名：DOUBAN, 第一列名：ID，  第二列名： 书名(Book_Name)，  第三列名：评分(Score)]
            sql = """CREATE TABLE DOUBAN (
                     ID  INT NOT NULL AUTO_INCREMENT,
                     Book_Name  VARCHAR(100) NOT NULL,
                     Score FLOAT(2,1),
                     PRIMARY KEY (ID))
                     ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
            cursor.execute(sql)  # 创建数据表
        except pymysql.err.InternalError as err:
            print(err)
        finally:
            for page in range(0, 3):  # 准备爬取前3页，每一页有20条记录
                dataList = loadDoubanScore(url, tag="小说", start=page * 20)  # 调用前面的爬取数据的方法
                for item in dataList:
                    # sql = "INSERT INTO `DOUBAN` (`Book_Name`, `Score`) VALUES ("活着", 9.3)"
                    sql = "INSERT INTO `DOUBAN` (`Book_Name`, `Score`) VALUES " + str(item)
                    cursor.execute(sql)
    connection.commit()
    connection.close()
